{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Guest House - Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "Coincidence. Have two guests with the same surname ever stayed in the hotel on the evening? Show the last name and both first names. Do not include duplicates.\n",
    "\n",
    "```\n",
    "+-----------+------------+-------------+\n",
    "| last_name | first_name | first_name  |\n",
    "+-----------+------------+-------------+\n",
    "| Davies    | Philip     | David T. C. |\n",
    "| Evans     | Graham     | Mr Nigel    |\n",
    "| Howarth   | Mr George  | Sir Gerald  |\n",
    "| Jones     | Susan Elan | Mr Marcus   |\n",
    "| Lewis     | Clive      | Dr Julian   |\n",
    "| McDonnell | John       | Dr Alasdair |\n",
    "+-----------+------------+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>last_name</th>\n",
       "        <th>first_name1</th>\n",
       "        <th>first_name2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Davies</td>\n",
       "        <td>Philip</td>\n",
       "        <td>David T. C.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Evans</td>\n",
       "        <td>Graham</td>\n",
       "        <td>Mr Nigel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Howarth</td>\n",
       "        <td>Mr George</td>\n",
       "        <td>Sir Gerald</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jones</td>\n",
       "        <td>Susan Elan</td>\n",
       "        <td>Mr Marcus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lewis</td>\n",
       "        <td>Clive</td>\n",
       "        <td>Dr Julian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>McDonnell</td>\n",
       "        <td>John</td>\n",
       "        <td>Dr Alasdair</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Davies', 'Philip', 'David T. C.'),\n",
       " ('Evans', 'Graham', 'Mr Nigel'),\n",
       " ('Howarth', 'Mr George', 'Sir Gerald'),\n",
       " ('Jones', 'Susan Elan', 'Mr Marcus'),\n",
       " ('Lewis', 'Clive', 'Dr Julian'),\n",
       " ('McDonnell', 'John', 'Dr Alasdair')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (SELECT * \n",
    "  FROM guest JOIN booking ON guest.id=booking.guest_id\n",
    ")\n",
    "SELECT DISTINCT b.last_name, b.first_name first_name1, a.first_name first_name2\n",
    "  FROM t a JOIN t b ON a.last_name = b.last_name\n",
    "    WHERE (\n",
    "        ((a.booking_date BETWEEN b.booking_date AND \n",
    "          DATE_ADD(b.booking_date, b.nights-1)) OR\n",
    "         (b.booking_date BETWEEN a.booking_date AND \n",
    "          DATE_ADD(a.booking_date, a.nights-1)))\n",
    "        AND (a.id >= b.id) AND\n",
    "        (a.first_name <> b.first_name)\n",
    "    )\n",
    "    ORDER BY b.last_name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "Check out per floor. The first digit of the room number indicates the floor – e.g. room 201 is on the 2nd floor. For each day of the week beginning 2016-11-14 show how many rooms are being vacated that day by floor number. Show all days in the correct order.\n",
    "\n",
    "```\n",
    "+------------+-----+-----+-----+\n",
    "| i          | 1st | 2nd | 3rd |\n",
    "+------------+-----+-----+-----+\n",
    "| 2016-11-14 |   5 |   3 |   4 |\n",
    "| 2016-11-15 |   6 |   4 |   1 |\n",
    "| 2016-11-16 |   2 |   2 |   4 |\n",
    "| 2016-11-17 |   5 |   3 |   6 |\n",
    "| 2016-11-18 |   2 |   3 |   2 |\n",
    "| 2016-11-19 |   5 |   5 |   1 |\n",
    "| 2016-11-20 |   2 |   2 |   2 |\n",
    "+------------+-----+-----+-----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>i</th>\n",
       "        <th>1st</th>\n",
       "        <th>2nd</th>\n",
       "        <th>3rd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-14</td>\n",
       "        <td>5</td>\n",
       "        <td>3</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-15</td>\n",
       "        <td>6</td>\n",
       "        <td>4</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-16</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-17</td>\n",
       "        <td>5</td>\n",
       "        <td>3</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-18</td>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-19</td>\n",
       "        <td>5</td>\n",
       "        <td>5</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-20</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2016-11-14', 5, 3, 4),\n",
       " ('2016-11-15', 6, 4, 1),\n",
       " ('2016-11-16', 2, 2, 4),\n",
       " ('2016-11-17', 5, 3, 6),\n",
       " ('2016-11-18', 2, 3, 2),\n",
       " ('2016-11-19', 5, 5, 1),\n",
       " ('2016-11-20', 2, 2, 2)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT checkout_date i, \n",
    "    SUM(CASE WHEN floor='1' THEN 1 ELSE 0 END) AS `1st`, \n",
    "    SUM(CASE WHEN floor='2' THEN 1 ELSE 0 END) AS `2nd`, \n",
    "    SUM(CASE WHEN floor='3' THEN 1 ELSE 0 END) AS `3rd`\n",
    "FROM \n",
    "(SELECT DATE_ADD(booking_date, nights) checkout_date, SUBSTRING(room_no, 1, 1) floor \n",
    "   FROM booking \n",
    "   WHERE DATE_ADD(booking_date, nights) BETWEEN '2016-11-14' AND\n",
    "     DATE_ADD('2016-11-14', 6)\n",
    ") AS t\n",
    "GROUP BY checkout_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>i</th>\n",
       "        <th>1st</th>\n",
       "        <th>2nd</th>\n",
       "        <th>3rd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-14</td>\n",
       "        <td>5</td>\n",
       "        <td>3</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-15</td>\n",
       "        <td>6</td>\n",
       "        <td>4</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-16</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-17</td>\n",
       "        <td>5</td>\n",
       "        <td>3</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-18</td>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-19</td>\n",
       "        <td>5</td>\n",
       "        <td>5</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-20</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2016-11-14', 5, 3, 4),\n",
       " ('2016-11-15', 6, 4, 1),\n",
       " ('2016-11-16', 2, 2, 4),\n",
       " ('2016-11-17', 5, 3, 6),\n",
       " ('2016-11-18', 2, 3, 2),\n",
       " ('2016-11-19', 5, 5, 1),\n",
       " ('2016-11-20', 2, 2, 2)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT DATE_ADD(booking_date, nights) i, \n",
    "       SUBSTRING(room_no, 1, 1) floor,\n",
    "       COUNT(*) n\n",
    "       FROM booking \n",
    "       WHERE DATE_ADD(booking_date, nights) BETWEEN '2016-11-14' AND\n",
    "          DATE_ADD('2016-11-14', 6)\n",
    "       GROUP BY DATE_ADD(booking_date, nights), SUBSTRING(room_no, 1, 1)\n",
    ")\n",
    "SELECT i,\n",
    "    COLLECT_SET(kv['1'])[0] AS `1st`,\n",
    "    COLLECT_SET(kv['2'])[0] AS `2nd`,\n",
    "    COLLECT_SET(kv['3'])[0] AS `3rd`\n",
    "    FROM\n",
    "(SELECT i, MAP(floor, n) kv FROM t) t_\n",
    "GROUP BY i"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "Free rooms? List the rooms that are free on the day 25th Nov 2016.\n",
    "\n",
    "```\n",
    "+-----+\n",
    "| id  |\n",
    "+-----+\n",
    "| 207 |\n",
    "| 210 |\n",
    "| 304 |\n",
    "+-----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>304</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(207,), (210,), (304,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT id FROM room LEFT JOIN (\n",
    "    SELECT room_no FROM booking \n",
    "    WHERE booking_date <= '2016-11-25' AND \n",
    "        DATE_ADD(booking_date, nights-1) >= '2016-11-25') t\n",
    "    ON room.id=t.room_no\n",
    "    WHERE t.room_no IS NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "Single room for three nights required. A customer wants a single room for three consecutive nights. Find the first available date in December 2016.\n",
    "\n",
    "```\n",
    "+-----+------------+\n",
    "| id  | MIN(i)     |\n",
    "+-----+------------+\n",
    "| 201 | 2016-12-11 |\n",
    "+-----+------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>room_no</th>\n",
       "        <th>this_booking</th>\n",
       "        <th>this_nights</th>\n",
       "        <th>next_booking</th>\n",
       "        <th>diff</th>\n",
       "        <th>next_nights</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>2016-12-03</td>\n",
       "        <td>5</td>\n",
       "        <td>2016-12-08</td>\n",
       "        <td>0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>2016-12-08</td>\n",
       "        <td>2</td>\n",
       "        <td>2016-12-10</td>\n",
       "        <td>0</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>2016-12-10</td>\n",
       "        <td>5</td>\n",
       "        <td>2016-12-15</td>\n",
       "        <td>0</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>201</td>\n",
       "        <td>2016-12-01</td>\n",
       "        <td>2</td>\n",
       "        <td>2016-12-03</td>\n",
       "        <td>0</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>201</td>\n",
       "        <td>2016-12-03</td>\n",
       "        <td>4</td>\n",
       "        <td>2016-12-07</td>\n",
       "        <td>0</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>301</td>\n",
       "        <td>2016-12-02</td>\n",
       "        <td>2</td>\n",
       "        <td>2016-12-04</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>301</td>\n",
       "        <td>2016-12-04</td>\n",
       "        <td>1</td>\n",
       "        <td>2016-12-05</td>\n",
       "        <td>0</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>301</td>\n",
       "        <td>2016-12-05</td>\n",
       "        <td>5</td>\n",
       "        <td>2016-12-12</td>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(101, '2016-12-03', 5, '2016-12-08', 0, 2),\n",
       " (101, '2016-12-08', 2, '2016-12-10', 0, 5),\n",
       " (101, '2016-12-10', 5, '2016-12-15', 0, 3),\n",
       " (201, '2016-12-01', 2, '2016-12-03', 0, 4),\n",
       " (201, '2016-12-03', 4, '2016-12-07', 0, 4),\n",
       " (301, '2016-12-02', 2, '2016-12-04', 0, 1),\n",
       " (301, '2016-12-04', 1, '2016-12-05', 0, 5),\n",
       " (301, '2016-12-05', 5, '2016-12-12', 2, 1)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS ( -- Step 1, build a query to show the bookings with the next ones\n",
    "    SELECT a.room_no, a.booking_date this_booking, a.nights this_nights, \n",
    "      MIN(b.booking_date) AS next_booking, \n",
    "      DATEDIFF(MIN(b.booking_date), a.booking_date) - a.nights AS diff\n",
    "    FROM booking a LEFT JOIN booking b ON (a.room_no=b.room_no)\n",
    "    WHERE a.room_type_requested='single' AND\n",
    "      DATE_FORMAT(a.booking_date, 'yyyyMM')='201612' AND \n",
    "      DATE_FORMAT(b.booking_date, 'yyyyMM')='201612' AND\n",
    "      a.booking_date < b.booking_date\n",
    "    GROUP BY a.room_no, a.nights, a.booking_date\n",
    ")\n",
    "\n",
    "SELECT t.*, booking.next_nights FROM \n",
    "  t LEFT JOIN \n",
    "    (SELECT nights next_nights, booking_date, room_no FROM booking) AS booking ON \n",
    "    (t.next_booking=booking.booking_date AND booking.room_no=t.room_no)\n",
    "    ORDER BY room_no, this_booking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>room_no</th>\n",
       "        <th>i</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>201</td>\n",
       "        <td>2016-12-11</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(201, '2016-12-11')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS ( \n",
    "    SELECT a.room_no, a.booking_date this_booking,\n",
    "      a.nights this_nights, \n",
    "      MIN(b.booking_date) AS next_booking, \n",
    "      DATEDIFF(MIN(b.booking_date), a.booking_date) - a.nights AS diff\n",
    "    FROM booking a LEFT JOIN booking b ON (a.room_no=b.room_no)\n",
    "    WHERE a.room_type_requested='single' AND\n",
    "      DATE_FORMAT(a.booking_date, 'yyyyMM')='201612' AND \n",
    "      DATE_FORMAT(b.booking_date, 'yyyyMM')='201612' AND\n",
    "      a.booking_date < b.booking_date\n",
    "    GROUP BY a.room_no, a.nights, a.booking_date\n",
    "), tt AS (\n",
    "    SELECT t.*, booking.next_nights FROM \n",
    "      t LEFT JOIN \n",
    "    (SELECT nights next_nights, booking_date, \n",
    "      room_no FROM booking) AS booking ON \n",
    "    (t.next_booking=booking.booking_date AND \n",
    "      booking.room_no=t.room_no)\n",
    ")\n",
    "-- Step 2, use this query to filter diff>3 or last checkout date is >3 days prior to end of month.\n",
    "SELECT * FROM (\n",
    "    SELECT room_no, \n",
    "      DATE_ADD(next_booking, next_nights) i FROM tt \n",
    "      WHERE diff >=3 \n",
    "    UNION ALL\n",
    "    SELECT room_no, \n",
    "      MAX(DATE_ADD(next_booking, next_nights)) i FROM tt \n",
    "        GROUP BY room_no\n",
    "        HAVING DAY(MAX(DATE_ADD(next_booking, next_nights))) < 31-3) c\n",
    "    ORDER BY c.i\n",
    "    LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "Gross income by week. Money is collected from guests when they leave. For each Thursday in November and December 2016, show the total amount of money collected from the previous Friday to that day, inclusive.\n",
    "\n",
    "```\n",
    "+------------+---------------+\n",
    "| Thursday   | weekly_income |\n",
    "+------------+---------------+\n",
    "| 2016-11-03 |          0.00 |\n",
    "| 2016-11-10 |      12608.94 |\n",
    "| 2016-11-17 |      13552.56 |\n",
    "| 2016-11-24 |      12929.69 |\n",
    "| 2016-12-01 |      11685.14 |\n",
    "| 2016-12-08 |      13093.79 |\n",
    "| 2016-12-15 |       8975.87 |\n",
    "| 2016-12-22 |       1395.77 |\n",
    "| 2016-12-29 |          0.00 |\n",
    "| 2017-01-05 |          0.00 |\n",
    "+------------+---------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>thursday</th>\n",
       "        <th>weekly_income</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-03</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-10</td>\n",
       "        <td>12608.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-17</td>\n",
       "        <td>13552.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-24</td>\n",
       "        <td>12929.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-12-01</td>\n",
       "        <td>11685.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-12-08</td>\n",
       "        <td>13093.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-12-15</td>\n",
       "        <td>8975.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-12-22</td>\n",
       "        <td>1395.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-12-29</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2016-11-03', 0.0),\n",
       " ('2016-11-10', 12608.94),\n",
       " ('2016-11-17', 13552.56),\n",
       " ('2016-11-24', 12929.69),\n",
       " ('2016-12-01', 11685.14),\n",
       " ('2016-12-08', 13093.79),\n",
       " ('2016-12-15', 8975.87),\n",
       " ('2016-12-22', 1395.77),\n",
       " ('2016-12-29', 0.0)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH income AS (  -- income: gross income by day\n",
    "    SELECT checkout, amount, \n",
    "        CASE WHEN DATE_FORMAT(checkout, 'u')<4 THEN \n",
    "            DATE_ADD(checkout, CAST(4-DATE_FORMAT(checkout, 'u') AS INT) )\n",
    "        WHEN DATE_FORMAT(checkout, 'u')>4 THEN\n",
    "            DATE_ADD(checkout, CAST(11-DATE_FORMAT(checkout, 'u') AS INT))\n",
    "        ELSE checkout END thu \n",
    "    FROM\n",
    "    (SELECT a.checkout, SUM(COALESCE(a.amount, 0)) amount FROM\n",
    "      (SELECT booking_date, DATE_ADD(booking_date, nights) checkout,\n",
    "        SUM(rate.amount * booking.nights) amount\n",
    "        FROM booking LEFT JOIN rate ON (\n",
    "          booking.occupants=rate.occupancy AND \n",
    "          booking.room_type_requested=rate.room_type)\n",
    "        GROUP BY booking_date, DATE_ADD(booking_date, nights)\n",
    "       UNION ALL\n",
    "       SELECT booking_date, DATE_ADD(booking_date, nights) checkout,\n",
    "           SUM(extra.amount) amount\n",
    "           FROM booking LEFT JOIN extra ON (\n",
    "                booking.booking_id=extra.booking_id)\n",
    "           GROUP BY booking.booking_date, DATE_ADD(booking_date, nights)\n",
    "      ) AS a\n",
    "    GROUP BY a.checkout) b\n",
    "), thursdays AS (  -- thursdays: Thursdays in Nov & Dec\n",
    "    SELECT DATE_ADD(\"2016-11-1\", a.pos) as thu\n",
    "      FROM (SELECT POSEXPLODE(SPLIT(REPEAT(\"o\", DATEDIFF(\"2016-12-31\", \"2016-11-1\")), \"o\"))) a\n",
    "      WHERE DATE_FORMAT(DATE_ADD('2006-11-1', a.pos), 'u')=5\n",
    ")\n",
    "\n",
    "SELECT thursdays.thu thursday, ROUND(SUM(COALESCE(income.amount, 0)), 2) weekly_income\n",
    "  FROM income RIGHT JOIN thursdays ON (income.thu=thursdays.thu)\n",
    "    WHERE income.amount IS NULL OR\n",
    "      income.checkout BETWEEN DATE_SUB(thursdays.thu, 6) AND thursdays.thu \n",
    "    GROUP BY thursdays.thu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
